/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package application.dao;

import application.model.EntityLookUp;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.ArrayList;
import java.util.HashMap;

/**
 *
 * @author aa6345
 */
@Repository("entityLookUpDao")
public class EntityLookUpDaoImpl implements EntityLookUpDao {

    private String userInputSearchTerm1 = "";  

    @Autowired
    private SessionFactory sessionFactory;

    // To get an entity by name/description
    @SuppressWarnings("unchecked")
    public List<EntityLookUp> listSelectedEntityLookUpResults(HashMap<String,String> userInputHashMap) {

        //TODO: Somehow get Connection since sessionFactory.getCurrentSession().connection() is deprecated
        //TODO: Need this to use PreparedStatement which gards against SQL Injection
        /*        
        String selectStatement = "SELECT * FROM User WHERE userId = ? ";
        PreparedStatement prepStmt = sessionFactory.getCurrentSession().connection().prepareStatement(selectStatement);
        prepStmt.setString(1, userInputHasMap);
        ResultSet rs = prepStmt.executeQuery();
         */

        List<EntityLookUp> selectedEntityAccompsCont = new ArrayList<EntityLookUp>();

        this.userInputSearchTerm1 = userInputHashMap.get("searchTerm1").replace("'", "");
        //this.userInputSearchTerm1 = userInputHashMap.get("searchTerm1").replace(" ", "");
        //this.userInputSearchTerm1 = this.userInputSearchTerm1.replace("'", "\'");

        //Replaced by JOIN query.  This version doesn't return records that have no rows in 'contactdetails'
        /*
        String searchQuery = "select `me`.`entityID` as entityID, "
                    + "`c`.`email` as entityEmail,  "
                    + "`me`.`fname` as entityFname, "
                    + "`me`.`mname` as entityMname, "
                    + "`me`.`sname` as entitySname, "
                    + "`me`.`active` as entityActive, "
                    + "`et`.`description` as entityTypeDescription "
                    + "from contactdetails c, mainentity me, entitytypes et "                    
                    + "where Concat_WS(' ',me.fname,me.mname,me.sname) = '"+this.userInputSearchTerm1+"' "
                    + "and c.entityID=me.entityID and me.entityTypeID=et.entityTypeID ";
        */
        String searchQuery = "SELECT `me`.`entityID` as entityID, `c`.`email` as entityEmail,  "
                + "`me`.`fname` as entityFname, `me`.`mname` as entityMname, `me`.`sname` as entitySname, "
                + "`me`.`active` as entityActive, `et`.`description` as entityTypeDescription, "
                + "`c`.`entityDescription` as entityDescription  "
                + "FROM mainentity me "
                + "LEFT JOIN entitytypes et on me.entityTypeID=et.entityTypeID "
                + "LEFT JOIN contactdetails c on c.entityID=me.entityID "
                + "WHERE Concat_WS(' ',me.fname,me.mname,me.sname) ='"+this.userInputSearchTerm1+"' ";

        System.out.println("*****THE QUERY WOULD BE: " + searchQuery + " + " + this.userInputSearchTerm1);

        selectedEntityAccompsCont = sessionFactory.getCurrentSession().createSQLQuery(searchQuery).addEntity(EntityLookUp.class).list();

        System.out.println("*****THE QUERY returned: "  + selectedEntityAccompsCont.size() + " recods");
        
        /*If top search empty then try to reverse sname,mname,fname*/
        if(selectedEntityAccompsCont == null || selectedEntityAccompsCont.isEmpty()) {
            /*
            searchQuery = "select `me`.`entityID` as entityID, "
                    + "`c`.`email` as entityEmail,  "
                    + "`me`.`fname` as entityFname, "
                    + "`me`.`mname` as entityMname, "
                    + "`me`.`sname` as entitySname, "
                    + "`me`.`active` as entityActive, "
                    + "`et`.`description` as entityTypeDescription "
                    + "from contactdetails c, mainentity me, entitytypes et "
                    + "where Concat_WS(' ',me.sname,me.mname,me.fname) = '"+this.userInputSearchTerm1+"' "
                    + "and c.entityID=me.entityID and me.entityTypeID=et.entityTypeID ";
             * 
             */
            searchQuery = "SELECT `me`.`entityID` as entityID, `c`.`email` as entityEmail,  "
                + "`me`.`fname` as entityFname, `me`.`mname` as entityMname, `me`.`sname` as entitySname, "
                + "`me`.`active` as entityActive, `et`.`description` as entityTypeDescription, "
                + "`c`.`entityDescription` as entityDescription  "
                + "FROM mainentity me "
                + "LEFT JOIN entitytypes et on me.entityTypeID=et.entityTypeID "
                + "LEFT JOIN contactdetails c on c.entityID=me.entityID "
                + "WHERE Concat_WS(' ',me.sname,me.mname,me.fname) ='"+this.userInputSearchTerm1+"' ";

            selectedEntityAccompsCont = sessionFactory.getCurrentSession().createSQLQuery(searchQuery).addEntity(EntityLookUp.class).list();
            System.out.println("*****THE QUERY returned: "  + selectedEntityAccompsCont.size() + " recods");
        }

        /*If search empty then split input on " " (space) and try to search for individual sname,mname,fname*/
        //TODO: If string split array has more than 4 elements do no do the search.  Security against spam.
        if(selectedEntityAccompsCont == null || selectedEntityAccompsCont.isEmpty()) {
            String[] inputArray = this.userInputSearchTerm1.split(" ");
            if(inputArray != null && inputArray.length < 5) {

                String searchString = "(";
                for (int i = 0; i < inputArray.length; i++) {
                    searchString += "(me.sname LIKE '"+inputArray[i]+"' OR me.mname LIKE '"+inputArray[i]+"' OR me.fname LIKE '"+inputArray[i]+"')";
                    if(inputArray.length != i + 1) {
                        searchString += " OR ";
                    }
                }
                searchString += ")";

                /*
                searchQuery = "select `me`.`entityID` as entityID, "
                        + "`c`.`email` as entityEmail,  "
                        + "`me`.`fname` as entityFname, "
                        + "`me`.`mname` as entityMname, "
                        + "`me`.`sname` as entitySname, "
                        + "`me`.`active` as entityActive, "
                        + "`et`.`description` as entityTypeDescription "
                        + "from contactdetails c, mainentity me, entitytypes et "
                        + "where "+searchString
                        + "and c.entityID=me.entityID and me.entityTypeID=et.entityTypeID ";
                */
                searchQuery = "SELECT `me`.`entityID` as entityID, `c`.`email` as entityEmail,  "
                + "`me`.`fname` as entityFname, `me`.`mname` as entityMname, `me`.`sname` as entitySname, "
                + "`me`.`active` as entityActive, `et`.`description` as entityTypeDescription, "
                + "`c`.`entityDescription` as entityDescription  "
                + "FROM mainentity me "
                + "LEFT JOIN entitytypes et on me.entityTypeID=et.entityTypeID "
                + "LEFT JOIN contactdetails c on c.entityID=me.entityID "
                + "WHERE "+searchString+" ";

                selectedEntityAccompsCont = sessionFactory.getCurrentSession().createSQLQuery(searchQuery).addEntity(EntityLookUp.class).list();
                System.out.println("*****THE QUERY returned: "  + selectedEntityAccompsCont.size() + " recods");
            }
        }
        
        return selectedEntityAccompsCont;
    }

    // To get an entity by entityID
    @SuppressWarnings("unchecked")
    public List<EntityLookUp> listSelectedEntityLookUpByIDResults(String entityID) {
        //TODO: Somehow get Connection since sessionFactory.getCurrentSession().connection() is deprecated
        //TODO: Need this to use PreparedStatement which gards against SQL Injection
        /*
        String selectStatement = "SELECT * FROM User WHERE userId = ? ";
        PreparedStatement prepStmt = sessionFactory.getCurrentSession().connection().prepareStatement(selectStatement);
        prepStmt.setString(1, userInputHasMap);
        ResultSet rs = prepStmt.executeQuery();
         */

        List<EntityLookUp> selectedEntityAccompsCont = new ArrayList<EntityLookUp>();

        this.userInputSearchTerm1 = entityID;
        //this.userInputSearchTerm1 = userInputHashMap.get("searchTerm1").replace(" ", "");
        //this.userInputSearchTerm1 = this.userInputSearchTerm1.replace("'", "\'");

        /*String searchQuery = "select `me`.`entityID` as entityID, "
                    + "`c`.`email` as entityEmail,  "
                    + "`me`.`fname` as entityFname, "
                    + "`me`.`mname` as entityMname, "
                    + "`me`.`sname` as entitySname, "
                    + "`me`.`active` as entityActive, "
                    + "`et`.`description` as entityTypeDescription "
                    + "from contactdetails c, mainentity me, entitytypes et "
                    + "where me.entityID = "+this.userInputSearchTerm1+" "
                    + "and c.entityID=me.entityID and me.entityTypeID=et.entityTypeID ";*/
        String searchQuery = "select `me`.`entityID` as entityID, "
                    + "`c`.`email` as entityEmail,  "
                    + "`c`.`entityDescription` as entityDescription,  "
                    + "`me`.`fname` as entityFname, "
                    + "`me`.`mname` as entityMname, "
                    + "`me`.`sname` as entitySname, "
                    + "`me`.`active` as entityActive, "
                    + "`et`.`description` as entityTypeDescription "
                + "FROM mainentity me "
                + "LEFT JOIN entitytypes et on me.entityTypeID=et.entityTypeID "
                + "LEFT JOIN contactdetails c on c.entityID=me.entityID "
                + "WHERE me.entityID = "+this.userInputSearchTerm1;

        System.out.println("*****THE QUERY WOULD BE: " + searchQuery + " + " + this.userInputSearchTerm1);

        selectedEntityAccompsCont = sessionFactory.getCurrentSession().createSQLQuery(searchQuery).addEntity(EntityLookUp.class).list();

        System.out.println("*****THE QUERY returned: "  + selectedEntityAccompsCont.size() + " recods");

        return selectedEntityAccompsCont;
    }
}
